package com.ruoyi.buy.service.impl;

import com.ruoyi.buy.domain.entity.PO.BuyMaterialBillItemPO;
import com.ruoyi.buy.domain.entity.PO.BuyMaterialBillPO;
import com.ruoyi.buy.domain.entity.PO.BuySpaPricePO;
import com.ruoyi.buy.domain.entity.buy0002.Buy0002SaveVO;
import com.ruoyi.buy.domain.entity.buy0002.Buy0002SuggestData;
import com.ruoyi.buy.domain.entity.buy0002.Buy0003SpaImportDataNew;
import com.ruoyi.buy.domain.entity.importPO.BuyMaterialBillItemImport;
import com.ruoyi.buy.mapper.PO.BuyMaterialBillItemMapper;
import com.ruoyi.buy.mapper.PO.BuyMaterialBillMapper;
import com.ruoyi.buy.mapper.PO.buy0002.Buy0002Mapper;
import com.ruoyi.buy.service.IBuy0001Service;
import com.ruoyi.buy.service.IBuy0002Service;
import com.ruoyi.buy.service.ICommonService;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 参数配置 服务层实现
 * 
 * @author ruoyi
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class Buy0002ServiceImpl implements IBuy0002Service
{
    @Resource
    private BuyMaterialBillMapper buyMaterialBillMapper;

    @Resource
    private BuyMaterialBillItemMapper buyMaterialBillItemMapper;

    @Resource
    private IBuy0001Service buy0001Service;

    @Resource
    private ICommonService iCommonService;

    @Resource
    private Buy0002Mapper buy0002Mapper;


    @Override
    public List<BuyMaterialBillPO> selectMaterialBillList(BuyMaterialBillPO param) {
        List<BuyMaterialBillPO> result = this.buyMaterialBillMapper.selectMaterialBillCondition(param);
        return result;
    }

    @Override
    public int save(Buy0002SaveVO para) throws Exception {
        //判断数据是新增还是删除

        BuyMaterialBillPO billPO = para.getBillPO();
        if (billPO.getiD() == null || billPO.getiD() ==0){
            this.addSave(para);
        }else{
            this.updateSave(para);
        }
        return 1;
    }

    @Override
    public Buy0002SaveVO view(BuyMaterialBillPO para) throws Exception {
        Buy0002SaveVO result = new Buy0002SaveVO();

        //先查主表
        BuyMaterialBillPO mainBill = this.buyMaterialBillMapper.selectMaterialBillById(para.getiD());
        if (mainBill != null && mainBill.getiD() != null && mainBill.getiD() != 0){
            BuyMaterialBillItemPO condition = new BuyMaterialBillItemPO();
            condition.setnMainId(mainBill.getiD());
            List<BuyMaterialBillItemPO> itemPOList = this.buyMaterialBillItemMapper.selectMaterialBillItemCondition(condition);

            //如果明细价格为空，去物料表里再查一下
            for (BuyMaterialBillItemPO item:itemPOList
                 ) {
                if (item.getnPrice() == null || item.getnPrice().compareTo(BigDecimal.ZERO) == 0){
                    //没有名称或者规格型号的直接跳过，没有品牌的机加件继续，外购件跳过
                    if (StringUtils.isEmpty(item.getvSpaName()) || StringUtils.isEmpty(item.getvSpaModel()) ){
                        continue;
                    }
                    if (item.getnPrice() == null || item.getnPrice().compareTo(BigDecimal.ZERO) == 0) {
                        BuySpaPricePO buySpaPricePO = new BuySpaPricePO();
                        buySpaPricePO.setvSpaName(item.getvSpaName());
                        buySpaPricePO.setvSpaModel(item.getvSpaModel());

                        if (item.getvKind().equals("02")) {
                            if (StringUtils.isEmpty(item.getvSpaBrand())) {
                                continue;
                            } else {
                                buySpaPricePO.setvSpaBrand(item.getvSpaBrand());
                            }

                        }

                        BuySpaPricePO pricePO = this.buy0001Service.selectSpaByCondition(buySpaPricePO);
                        //单价
                        if (pricePO == null || pricePO.getnPrice() == null) {
                            item.setnPrice(BigDecimal.ZERO);
                        } else {
                            item.setnPrice(pricePO.getnPrice());
                        }
                    }
                }
            }
            result.setBillPO(mainBill);
            result.setItemPOList(itemPOList);
        }
        return result;
    }

    @Override
    public List<BuyMaterialBillItemImport> selectPrices(List<BuyMaterialBillItemImport> list) throws Exception {
        for (BuyMaterialBillItemImport item:list
             ) {
            //没有名称或者规格型号的直接跳过，没有品牌的机加件继续，外购件跳过
            if (StringUtils.isEmpty(item.getvSpaName()) || StringUtils.isEmpty(item.getvSpaModel()) ){
                continue;
            }
            if (item.getnPrice() == null || item.getnPrice().compareTo(BigDecimal.ZERO) == 0){
                BuySpaPricePO buySpaPricePO = new BuySpaPricePO();
                buySpaPricePO.setvSpaName(item.getvSpaName());
                buySpaPricePO.setvSpaModel(item.getvSpaModel());

                if (item.getvKind().equals("02")){
                    if (StringUtils.isEmpty(item.getvSpaBrand()) ){
                        continue;
                    }else{
                        buySpaPricePO.setvSpaBrand(item.getvSpaBrand());
                    }

                }

                BuySpaPricePO pricePO = this.buy0001Service.selectSpaByCondition(buySpaPricePO);
                //单价
                if (pricePO == null || pricePO.getnPrice() == null) {
                    item.setNPrice(BigDecimal.ZERO);
                }else{
                    item.setNPrice(pricePO.getnPrice());
                }
                //单套价格
                item.setNSetPrice(item.getnQty().multiply(item.getnPrice()));

                //总价
                item.setnTotalPrice(item.getnSetPrice().multiply(item.getnSetQty()));

                //总数量
                item.setNTotalQty(item.getnQty().multiply(item.getnSetQty()));
            }else{
                //单套价格
                item.setNSetPrice(item.getnQty().multiply(item.getnPrice()));

                //总价
                item.setnTotalPrice(item.getnSetPrice().multiply(item.getnSetQty()));

                //总数量
                item.setNTotalQty(item.getnQty().multiply(item.getnSetQty()));
            }
        }
        return list;
    }

    @Override
    public Map importData(List<MultipartFile> files, String updateSupport, String folderPath) throws Exception {
        Map map = new HashMap();
        String fileName = "";
        if (files == null || files.size() <= 0) {
            return map;
        }else {

            if (StringUtils.isNotEmpty(folderPath) || folderPath.equals("undefined")){
                folderPath = "D:/myFolder/" + System.currentTimeMillis();
                File dir = new File(folderPath);
                dir.mkdirs();
            }else{
                if ("1".equals(updateSupport)){
                    folderPath = "D:/myFolder/" + System.currentTimeMillis();
                    File dir = new File(folderPath);
                    dir.mkdirs();
                }
            }
            List<BuyMaterialBillItemImport> list = new ArrayList<>();
            List<BuyMaterialBillItemImport> result = new ArrayList<>();
            for (MultipartFile file:files
                 ) {
                /*获取文件原名称*/
                String originalFilename = file.getOriginalFilename();
                fileName = originalFilename;
                /*获取文件格式*/
                String fileFormat = originalFilename.substring(originalFilename.lastIndexOf("."));
                File toFile = null;
                toFile = new File(folderPath + File.separator + originalFilename);

                try {
                    //先把文件存起来
                    InputStream ins = file.getInputStream();

                    inputStreamToFile(ins, toFile);
                    ins.close();

                    //再读文件的数据
                    result = readExcel(toFile,result);


                } catch (IOException e) {
                    throw new Exception(e.toString());
                }

            }
            //再遍历list查单价
            list = this.selectPrices(result);
            map.put("folderPath",folderPath);
            map.put("list",list);
            map.put("isCover",updateSupport);
            map.put("vProjectDec",fileName.substring(0,fileName.indexOf(".")));
            return map;
        }
    }

    @Override
    public String exportData(Long id) throws Exception {
        BuyMaterialBillPO billPO = new BuyMaterialBillPO();
        billPO = this.buyMaterialBillMapper.selectMaterialBillById(id);
        List<String> allFiles = this.getAllFilesInFolder(billPO.getFolderPath());
        if (allFiles.isEmpty()){
            return null;
        }else{
            List<BuyMaterialBillItemImport> list = new ArrayList<>();
            for (String fileName:allFiles
                 ) {
                if(fileName.contains("生产") || fileName.contains("财务") || fileName.contains("销售")){
                    break;
                }

                try (
                        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(fileName));
                ){
                    // 获取第一个表单Sheet
                    XSSFSheet sheetAt = workbook.getSheetAt(0);
                    // 获取第一行为标题行
                    XSSFRow titleRow = sheetAt.getRow(0);
                    //根据标题判断这个是电气的询价还是机械的询价
                    XSSFCell titleCell = titleRow.getCell(0);

                    //再加一个客户建议品牌
                    List<Buy0002SuggestData> suggestDataList = new ArrayList<>();

                    //一下属性需要自动填写到Excel
                    //总数量
                    int nTotalQtyIndex = 14;
                    //单套数量
                    int nqtySetIndex = 8;
                    //单价
                    int nPriceIndex = 0;
                    //单套总价
                    int nSetPriceIndex = 0;
                    //总价
                    int nTotalIndex = 0;
                    //货期
                    int nDaysIndex = 0;
                    //采购报价选用品牌
                    int nSpaBrandIndex = 0;

                    //备注
                    int remarkIndex = 28;

                    for (Row row : sheetAt) {
                        // 读取每一行的单元格
                        if (row.getRowNum() == 0) {
                            continue;
                        }
                        if(row.getRowNum() == 1){
                            for (int j = 0; j < row.getLastCellNum(); j++) {
                                //单元格处理
                                XSSFCell cell = (XSSFCell) row.getCell(j);
                                String cellStr = cell.getStringCellValue();
                                if (cellStr.equals("*单价（含税13%)") || cellStr.equals("*技术选型单价（含税13%)")){
                                    nPriceIndex = j;
                                }else if (cellStr.equals("单套总价（含税13%）")){
                                    nSetPriceIndex = j;
                                }else if (cellStr.equals("总价（含税13%）")){
                                    nTotalIndex = j;
                                }else if (cellStr.equals("*货期")){
                                    nDaysIndex = j;
                                }else if (cellStr.equals("备注")){
                                    remarkIndex = j;
                                }
                            }
                            continue;
                        }
                        //名称和规格型号为空时不处理，直接跳过
                        if (row.getCell(6)==null || row.getCell(6).getCellType() == CellType.BLANK || row.getCell(6).getCellType() == CellType._NONE){
                            continue;
                        }
                        if (row.getCell(7)==null || row.getCell(7).getCellType() == CellType.BLANK || row.getCell(7).getCellType() == CellType._NONE){
                            continue;
                        }
                        //数据处理
                        BuyMaterialBillItemPO item = new BuyMaterialBillItemPO();
                        Buy0002SuggestData suggestData = new Buy0002SuggestData();
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            //单元格处理
                            XSSFCell cell = (XSSFCell) row.getCell(j);
                            //第一列序号不处理
                            if (j == 0){
                                continue;
                            }
                            //处理单元格值
                            String cellStr = "";
                            if (cell == null) {// 单元格为空设置cellStr为空串
                                cellStr = "";
                            } else if (cell.getCellType().equals(CellType.BOOLEAN)) {
                                // 对布尔值的处理
                                cellStr = String.valueOf(cell.getBooleanCellValue());
                            } else if (cell.getCellType().equals(CellType.NUMERIC)) {
                                // 对数字值的处理
                                cellStr = cell.getNumericCellValue() + "";
                                //如果最后两位是.0,就干掉，因为是默认把字符当成数字了
                                if (!StringUtils.isEmpty(cellStr) &&cellStr.length() > 2) {
                                    String lastTwo = cellStr.substring(cellStr.length() - 2);
                                    if (lastTwo.equals(".0")) {
                                        cellStr = cellStr.substring(0, cellStr.length() - 2);
                                    }
                                }

                            }else if (cell.getCellType().equals(CellType.FORMULA)) {
                                FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                                String cellFormula = cell.getCellFormula();
                                // 执行公式，此处cell的值就是公式
                                CellValue evaluate = formulaEvaluator.evaluate(cell);
                                cellStr = evaluate.formatAsString();
                            }
                            else {
                                // 其余按照字符串处理
                                cellStr = cell.getStringCellValue();
                            }
                            //名称
                            if (j == 6){
                                item.setvSpaName(cellStr);
                                suggestData.setvSpaName(cellStr);
                            }
                            //规格型号
                            if (j == 7){
                                item.setvSpaModel(cellStr);
                                suggestData.setvSpaModel(cellStr);
                            }
                            //技术选用品牌
                            if (j == 10){
                                item.setvSpaBrand(cellStr.trim());
                                suggestData.setvSelectBrand(cellStr.trim());
                            }
                        }




                        //如果表里没有价格，就去查，已有价格的不做变更

                        item.setnMainId(id);
                        List<BuyMaterialBillItemPO> searchList = this.buyMaterialBillItemMapper.selectMaterialBillItemCondition(item);
                        if(!searchList.isEmpty()){
                            BuyMaterialBillItemPO aa = searchList.get(0);
                            if(aa != null && aa.getnPrice() != null){
                                //总数量
                                double nTotalQty = row.getCell(nTotalQtyIndex).getNumericCellValue();
                                //单套数量
                                double nqtySet = 0;
                                if (row.getCell(nqtySetIndex).getCellType().equals(CellType.NUMERIC)){
                                     nqtySet = row.getCell(nqtySetIndex).getNumericCellValue();
                                }else{
                                    String vQtySet = row.getCell(nqtySetIndex).getStringCellValue();
                                     nqtySet = Double.valueOf(vQtySet);
                                }

                                //单价
                                row.getCell(nPriceIndex).setCellValue(aa.getnPrice() == null ? 0:aa.getnPrice().doubleValue());
                                double nPrice = aa.getnPrice() == null ? 0:aa.getnPrice().doubleValue();
                                 //单套总价
                                row.getCell(nSetPriceIndex).setCellValue(nPrice*nqtySet);
                                //总价
                                row.getCell(nTotalIndex).setCellValue(nPrice*nTotalQty);
                                //货期
                                row.getCell(nDaysIndex).setCellValue(aa.getnDays() == null ? "0": aa.getnDays().toString());
//                                //采购报价选用品牌
//                                row.getCell(nSpaBrandIndex).setCellValue(aa.getvSpaBrand() == null ? "": aa.getvSpaBrand());
                                //备注
                                row.getCell(remarkIndex).setCellValue(aa.getRemark() == null ? "": aa.getRemark());
                            }
                        }

                        //多查一个客户建议品牌
                        List<Buy0002SuggestData> listBySpa = this.buy0002Mapper.getSuggestData(suggestData);
                        if (!listBySpa.isEmpty()){
                            suggestDataList.addAll(listBySpa);
                        }
                    }

                    //查一下有没有客户建议品牌，有的话在最后新增一个sheet页，叫客户建议品牌，然后把信息录进去
                    if (!suggestDataList.isEmpty()){
                        writeSuggestSheet(workbook,suggestDataList);
                    }
                    try (FileOutputStream out = new FileOutputStream(fileName)) {
                        workbook.write(out);
                    }
                }
            }
        }
        return billPO.getFolderPath();
    }

    @Override
    public List<BuyMaterialBillItemPO> exportXJDData(List<Buy0003SpaImportDataNew> list, String updateBy, String billID) throws Exception {
        //遍历材料清单明细价格，直接覆盖价格
        for (Buy0003SpaImportDataNew item:list
             ) {
            //构建查询条件
            BuyMaterialBillItemPO searchPO = new BuyMaterialBillItemPO();
            searchPO.setvSpaName(item.getVSpaName());
            searchPO.setvSelectBrand(item.getVSelectBrand());
            searchPO.setvSpaModel(item.getVSpaModel());
            searchPO.setnMainId(Long.valueOf(billID));
            //查对应数据
            List<BuyMaterialBillItemPO> itemPOList = this.buyMaterialBillItemMapper.selectMaterialBillItemCondition(searchPO);
            //改价格
            if (!itemPOList.isEmpty()){
                itemPOList.get(0).setnPrice(item.getNLastPrice());
                itemPOList.get(0).setUpdateBy(updateBy);
                itemPOList.get(0).setUpdateTime(DateUtils.getNowDate());
                itemPOList.get(0).setvSpaBrand(item.getVSpaBrand());
                itemPOList.get(0).setnDays(item.getnDays());
                itemPOList.get(0).setSupplier(item.getSupplier());
                itemPOList.get(0).setvReplaceModel(item.getvReplaceModel());
                itemPOList.get(0).setnRePlacePrice(item.getnRePlacePrice());
                itemPOList.get(0).setvReplaceDays(item.getvReplaceDays());
                if (StringUtils.isEmpty(itemPOList.get(0).getvSpaBrand())){
                    if (StringUtils.isEmpty(item.getVSpaBrand())){
                        itemPOList.get(0).setvSpaBrand(item.getVSelectBrand());
                    }else{
                        itemPOList.get(0).setvSpaBrand(item.getVSpaBrand());
                    }
                }

                String vRemarks  = "";
                //备注
                if (!StringUtils.isEmpty(item.getvReplaceModel())){
                    vRemarks = "该型号厂家已停用，建议替代品型号为：" + item.getvReplaceModel();
                    if(item.getnRePlacePrice() != null && item.getnRePlacePrice().compareTo(BigDecimal.ZERO) != 0){
                        vRemarks = vRemarks + ",价格为：" ;
                        vRemarks = vRemarks + item.getnRePlacePrice().toString();
                    }
                    if (!StringUtils.isEmpty(item.getvReplaceDays())){
                        vRemarks = vRemarks + ",货期为：" ;
                        vRemarks = vRemarks + item.getvReplaceDays();
                    }
                    itemPOList.get(0).setRemark(vRemarks);

                }
                this.buyMaterialBillItemMapper.updateMaterialBillItemByID(itemPOList.get(0));
            }


            //要查个主表编号
            BuyMaterialBillPO billPO = this.buyMaterialBillMapper.selectMaterialBillById(Long.valueOf(billID));
            //再修改物料总表价格
            if(item.getnPrice() != null && item.getnPrice().compareTo(BigDecimal.ZERO) != 0){
                if (StringUtils.isEmpty(itemPOList.get(0).getvSpaBrand()) || itemPOList.get(0).getvSpaBrand().equals(itemPOList.get(0).getvSelectBrand())){
                    this.iCommonService.changeSpaPrice(null,itemPOList.get(0),updateBy,billPO.getvBillNo(),"BUY0002",null);
                }
            }

        }


        //反查数据给前台页面
        BuyMaterialBillItemPO searchPO = new BuyMaterialBillItemPO();
        searchPO.setnMainId(Long.valueOf(billID));
        //查对应数据
        List<BuyMaterialBillItemPO> itemPOList = this.buyMaterialBillItemMapper.selectMaterialBillItemCondition(searchPO);
        return itemPOList;
    }


    private static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private List<BuyMaterialBillItemImport> readExcel(File file,List<BuyMaterialBillItemImport> result) throws IOException {

        String excelType = "";
        InputStream in = new FileInputStream(file);
        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        // 获取第一行为标题行
        XSSFRow titleRow = sheetAt.getRow(0);
        //根据标题判断这个是电气的询价还是机械的询价
        XSSFCell titleCell = titleRow.getCell(0);
        String titleStr = titleCell.getStringCellValue();
        int nDaysIndex = 0;
        int remarkIndex = 0;
        int vHeadIndex = 0;
        // 循环读取某一行
        for (Row row : sheetAt) {

            // 读取每一行的单元格
            if (row.getRowNum() == 0) {
                continue;
            }
            if(row.getRowNum() == 1){
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    //单元格处理
                    XSSFCell cell = (XSSFCell) row.getCell(j);
                    String cellStr = cell.getStringCellValue();
                    if (cellStr.equals("*货期")){
                        nDaysIndex = j;
                    }else if (cellStr.equals("备注")){
                        remarkIndex = j;
                    }else if (cellStr.equals("*负责人")){
                        vHeadIndex = j;
                    }
                }
                continue;
            }
            //连续6格格为空，判断为这一行没有数据，跳过
            if (isCellNull(row.getCell(1)) && isCellNull(row.getCell(2))&& isCellNull(row.getCell(3))&&isCellNull(row.getCell(4))
            &&isCellNull(row.getCell(5)) && isCellNull(row.getCell(6))){
                break;
            }

            BuyMaterialBillItemImport item = new BuyMaterialBillItemImport();

            for (int j = 0; j < row.getLastCellNum(); j++) {
                //连续6格格为空，判断为这一行没有数据，跳过
                //单元格处理
                XSSFCell cell = (XSSFCell) row.getCell(j);
                if (j == 0){
                    continue;
                }
                //连续6格格为空，判断为这一行没有数据，跳过
                String cellStr = "";
                //判断这一行是不是合并单元格的项目
                if (isMergedRegion(sheetAt, row.getRowNum(), j)) {
                    cellStr = getMergedRegionValue(sheetAt, row.getRowNum(), j,sheets).trim();
                }else{
                    cellStr = getCellValue(cell,sheets).trim();
                }
                //装配线
                if (j == 1){
                    item.setVProductLine(cellStr);
                }
                //工序号
                if (j == 2){
                    item.setVProcessNo(cellStr);
                }
                //设备名称
                if (j == 3){
                    item.setVDeviceName(cellStr);
                }
                //主要部件
                if (j == 4){
                    item.setVMainPart(cellStr);
                }
                //类别
                if (j == 5){
                    if (cellStr.equals("机加")){
                        item.setVKind("01");
                    }else if (cellStr.equals("外购")){
                        item.setVKind("02");
                    }else if (cellStr.equals("通用")){
                        item.setVKind("03");
                    }else if (cellStr.equals("图纸")){
                        item.setVKind("04");
                    }else{
                        item.setVKind("02");
                    }
                }
                //物料名称
                if (j == 6){
                    item.setVSpaName(cellStr);
                }
                //规格型号
                if (j == 7){
                    if (!StringUtils.isEmpty(cellStr) &&cellStr.length() > 2){
                        //如果最后两位是.0,就干掉，因为是默认把字符当成数字了
                        String lastTwo = cellStr.substring(cellStr.length()-2);
                        if (lastTwo.equals(".0")){
                            cellStr = cellStr.substring(0,cellStr.length()-2);
                        }
                    }

                    item.setVSpaModel(cellStr);

                }
                //单套数量
                if (j == 8){
                    item.setNQty(new BigDecimal(StringUtils.isEmpty(cellStr)?"0":cellStr));
                }
                //单位
                if (j == 9){
                    item.setVUnit(cellStr);
                }
                //技术选用品牌
                if (j == 10){
                    item.setVSelectBrand(cellStr);
                }
                //客户推荐品牌
                if (j == 11){
                    item.setVSuggestBrand(cellStr);
                }
                //采购报价选用品牌
                if (j == 12){
                    item.setVSpaBrand(cellStr);
                    if (StringUtils.isEmpty(item.getvSpaBrand())){
                        item.setVSpaBrand(item.getvSelectBrand());
                    }
                }
                //套数
                if (j == 13){
                    item.setNSetQty(new BigDecimal(StringUtils.isEmpty(cellStr)?"0":cellStr));
                }
                //材料
                if (j == 15){
                    item.setVMaterial(cellStr);
                }
                //预计下单时间
                if (j == 16){
                    item.setVBuyDate(cellStr);
                }
                //预计到货时间
                if (j == 17){
                    item.setVArrivalDate(cellStr);
                }
                //单价
                if (j == 18){
                    item.setNPrice(new BigDecimal(StringUtils.isEmpty(cellStr)?"0":cellStr));
                }
                //货期
                if (j == nDaysIndex){
                    item.setNDays(StringUtils.isEmpty(cellStr)?"":cellStr);
                }
                //负责人
                if (j == vHeadIndex){
                    item.setVHeadName(cellStr);
                }
                //备注
                if (j == remarkIndex){
                    item.setRemark(cellStr);
                }
            }
            result.add(item);

        }
        in.close();
        return result;
    }

    /**
     *  @Description 先获取合并的区域块,然后根据遍历的行、列判断是否在某个区域范围内，是否是合并单元格
     *  @param   sheet  row  column
     *  @return  isMergedRegion
     *  @author  itw_lixd05
     */
    public  boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetNumMergedRegions = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetNumMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    private boolean isCellNull(Cell cell){
        if (cell.getCellType() == CellType.BLANK || cell.getCellType() == CellType._NONE){
            return  true;
        }else{
            return false;
        }

    }

    /*
     *  @Description 根据行号、列号定位cell获取值
     *  @param  cell
     *  @return  getCellValue
     *  @author  itw_lixd05
     */
    public static String getCellValue(Cell cell,XSSFWorkbook workbook) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        if (cell == null) {// 单元格为空设置cellStr为空串
            cellValue = "";
        } else if (cell.getCellType().equals(CellType.BOOLEAN)) {
            // 对布尔值的处理
            cellValue = String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType().equals(CellType.NUMERIC)) {
            // 对数字值的处理
            cellValue = cell.getNumericCellValue() + "";
            //如果最后两位是.0,就干掉，因为是默认把字符当成数字了
            if (!StringUtils.isEmpty(cellValue) &&cellValue.length() > 2) {
                String lastTwo = cellValue.substring(cellValue.length() - 2);
                if (lastTwo.equals(".0")) {
                    cellValue = cellValue.substring(0, cellValue.length() - 2);
                }
            }
        }else if (cell.getCellType().equals(CellType.FORMULA)) {
            FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
            String cellFormula = cell.getCellFormula();
            // 执行公式，此处cell的值就是公式
            CellValue evaluate = formulaEvaluator.evaluate(cell);
            cellValue = evaluate.formatAsString();
        }
        else {
            // 其余按照字符串处理
            cellValue = cell.getStringCellValue();
        }
        return cellValue;
    }


    /**
     *  @Description   通过确定当前行号、列号所在区域块,获取当前区域块第一个单元格的值,赋值给整个区域块的值
     *  因为如果遍历每一个行列如果是合并单元格，只有第一个单元格有值，其他的值为空字符串
     *  @param   sheet  row  column
     *  @return  getMergedRegionValue
     *  @author  itw_lixd05
     */
    public  String getMergedRegionValue(Sheet sheet, int row, int column,XSSFWorkbook sheets) {
        int sheetNumMergedRegions = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetNumMergedRegions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row row1 = sheet.getRow(firstRow);
                    Cell cell = row1.getCell(firstColumn);
                    return getCellValue(cell,sheets);
                }
            }
        }
        return null;
    }


    private int addSave(Buy0002SaveVO para) throws Exception {
        BuyMaterialBillPO billPO = para.getBillPO();
        billPO.setCreateBy(para.getCreateBy());

        String vBillNo = this.iCommonService.getNo("CL");
        billPO.setvBillNo(vBillNo);
        int mainAddFlag = this.buyMaterialBillMapper.insertMaterialBill(billPO);
        if (mainAddFlag!= 1){
            throw new Exception("系统异常，请联系管理员");
        }
        List<BuyMaterialBillPO> billPOs= this.buyMaterialBillMapper.selectMaterialBillCondition(billPO);
        if (billPOs.size()>0){
            Long nMainId = billPOs.get(0).getiD();
            List<BuyMaterialBillItemPO> itemPOList = para.getItemPOList();
            for (BuyMaterialBillItemPO item:itemPOList
                 ) {

                item.setnMainId(nMainId);
                item.setCreateBy(para.getCreateBy());
                int itemAddFlag = this.buyMaterialBillItemMapper.insertMaterialBillItem(item);
                if (itemAddFlag!= 1){
                    throw new Exception("系统异常，请联系管理员");
                }
                //如果规格型号或者品牌为空，保存记录，不做价格更新
                if(StringUtils.isEmpty(item.getvSpaModel())){
                    continue;
                }
                if(StringUtils.isEmpty(item.getvSpaBrand()) && item.getvKind().equals("02")){
                    continue;
                }
                //如果价钱不是0，更配件的价格表，先把就数据作废，然后再新增一条新的
                //20240325做变更，如果采购选用品牌为空，或者采购选用品牌和技术选用品牌一致的时候，才去变更物料表的价格

                if (item.getnPrice() != null && item.getnPrice().compareTo(BigDecimal.ZERO) != 0){
                    this.iCommonService.changeSpaPrice(null,item,para.getCreateBy(),billPOs.get(0).getvBillNo(),"BUY0002",null);
                }

            }

        }
        return 1;
    }

    private int updateSave(Buy0002SaveVO para) throws Exception {
        if(para.isCover()){
            isCoverUpdate(para);
        }else{
            isNotCoverUpdate(para);
        }
        return 1;
    }

    private void isCoverUpdate(Buy0002SaveVO para) throws Exception {
        //主表正常更新
        BuyMaterialBillPO billPO = para.getBillPO();
        billPO.setUpdateBy(para.getCreateBy());
        int mainUpdateFlag = this.buyMaterialBillMapper.updateMaterialBillByID(billPO);
        if (mainUpdateFlag != 1) {
            throw new Exception("系统异常，请联系管理员");
        }


        //所有已有子项都作废
        //1.查所有子项
        BuyMaterialBillItemPO condition = new BuyMaterialBillItemPO();
        condition.setnMainId(para.getBillPO().getiD());
        List<BuyMaterialBillItemPO> itemPOList = this.buyMaterialBillItemMapper.selectMaterialBillItemCondition(condition);
        //2作废
        for (BuyMaterialBillItemPO item : itemPOList
        ) {
            int itemUpdateFlag = 1;
            item.setUpdateBy(para.getCreateBy());
            item.setStatus("1");
            itemUpdateFlag = this.buyMaterialBillItemMapper.updateMaterialBillItemByID(item);
            if (itemUpdateFlag != 1) {
                throw new Exception("系统异常，请联系管理员");
            }
        }
        //3把新的子项新增进来
        for (BuyMaterialBillItemPO item : itemPOList
        ) {
            item.setnMainId(para.getBillPO().getiD());
            item.setCreateBy(para.getCreateBy());
            int itemAddFlag = this.buyMaterialBillItemMapper.insertMaterialBillItem(item);
            if (itemAddFlag != 1) {
                throw new Exception("系统异常，请联系管理员");
            }
            //如果规格型号或者品牌为空，保存记录，不做价格更新
            if(StringUtils.isEmpty(item.getvSpaBrand()) || StringUtils.isEmpty(item.getvSpaModel())){
                continue;
            }
            //如果价钱不是0，更配件的价格表，先把就数据作废，然后再新增一条新的
            if (item.getnPrice() != null && item.getnPrice().compareTo(BigDecimal.ZERO) != 0) {
                this.iCommonService.changeSpaPrice(null,item, para.getCreateBy(),para.getBillPO().getvBillNo(),"BUY0002",null);
            }
        }
    }

    private void isNotCoverUpdate(Buy0002SaveVO para) throws Exception{
        BuyMaterialBillPO billPO = para.getBillPO();
        billPO.setUpdateBy(para.getCreateBy());
        int mainUpdateFlag = this.buyMaterialBillMapper.updateMaterialBillByID(billPO);
        if (mainUpdateFlag!= 1){
            throw new Exception("系统异常，请联系管理员");
        }

        List<BuyMaterialBillItemPO> itemPOList = para.getItemPOList();
        for (BuyMaterialBillItemPO item:itemPOList
        ) {
            int itemUpdateFlag = 1;

            if(item.getiD() == null || item.getiD() == 0){
                item.setCreateBy(para.getCreateBy());
                item.setnMainId(billPO.getiD());
                itemUpdateFlag = this.buyMaterialBillItemMapper.insertMaterialBillItem(item);
            }else{
                item.setUpdateBy(para.getCreateBy());
                itemUpdateFlag = this.buyMaterialBillItemMapper.updateMaterialBillItemByID(item);
            }

            if (itemUpdateFlag!= 1){
                throw new Exception("系统异常，请联系管理员");
            }
            //如果规格型号或者品牌为空，保存记录，不做价格更新
            if(StringUtils.isEmpty(item.getvSpaBrand()) || StringUtils.isEmpty(item.getvSpaModel())){
                continue;
            }
            //如果价钱不是0，更配件的价格表，先把就数据作废，然后再新增一条新的
            if (item.getnPrice() != null && item.getnPrice().compareTo(BigDecimal.ZERO) != 0){
                this.iCommonService.changeSpaPrice(null,item,para.getCreateBy(),billPO.getvBillNo(),"BUY0002",null);
            }
        }
    }

    private   List<String> getAllFilesInFolder(String folderPath) {
        File folder = new File(folderPath);
        List<String> fileNames = new ArrayList<>();

        if (folder.exists() && folder.isDirectory()) {
            for (File file : folder.listFiles()) {
                String fileName = file.getName();

                // 判断文件名后缀为xls或者xlsx（根据自己的情况修改）
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    fileNames.add(file.getAbsolutePath());
                }
            }
        } else {
            System.out.println("文件夹不存在！");
        }
        return fileNames;
    }

    private Workbook writeSuggestSheet(Workbook wb,List<Buy0002SuggestData> suggestDataList){
        Sheet sheet = wb.getSheet("客户建议品牌");
        if (null != sheet){
            int sheetIndex = wb.getSheetIndex(sheet);
            wb.removeSheetAt(sheetIndex);
        }

        //新增一个sheet页
        Sheet sheetNew = wb.createSheet("客户建议品牌");

        //新建标题行
        sheetNew  = createTitleRow(sheetNew);
        //组件内容
        sheetNew = createRowData(sheetNew,suggestDataList);
        //设置默认显示第一页
        wb.setActiveSheet(0);
        return wb;
    }

    private Sheet createTitleRow(Sheet sheetNew){
        // 在第一个工作表上创建第1行
        Row row = sheetNew.createRow(0); // 行索引从0开始，1代表第二行
        // 在创建的行中创建单元格并设置值
        Cell cell1 = row.createCell(0);
        cell1.setCellValue("名称");
        sheetNew.setColumnWidth(0, 25 * 256 + 185);

        Cell cell2 = row.createCell(1);
        cell2.setCellValue("规格型号");
        sheetNew.setColumnWidth(1, 25 * 256 + 185);

        Cell cell3 = row.createCell(2);
        cell3.setCellValue("技术选用品牌");
        sheetNew.setColumnWidth(2, 25 * 256 + 185);

        Cell cell4 = row.createCell(3);
        cell4.setCellValue("客户推荐品牌");
        sheetNew.setColumnWidth(3, 25 * 256 + 185);

        Cell cell5 = row.createCell(4);
        cell5.setCellValue("推荐品名称");
        sheetNew.setColumnWidth(4, 25 * 256 + 185);

        Cell cell6 = row.createCell(5);
        cell6.setCellValue("推荐品规格型号");
        sheetNew.setColumnWidth(5, 25 * 256 + 185);

        Cell cell7 = row.createCell(6);
        cell7.setCellValue("推荐品价格");
        sheetNew.setColumnWidth(6, 10 * 256 + 185);

        Cell cell8 = row.createCell(7);
        cell8.setCellValue("备注");
        sheetNew.setColumnWidth(7, 25 * 256 + 185);

        return sheetNew;
    }

    private Sheet createRowData(Sheet sheetNew,List<Buy0002SuggestData> suggestDataList){
        for (int i = 0; i < suggestDataList.size(); i++) {
            Row row = sheetNew.createRow(i+1);
            Buy0002SuggestData item = suggestDataList.get(i);
            // 在创建的行中创建单元格并设置值
            Cell cell1 = row.createCell(0);
            cell1.setCellValue(item.getvSpaName());

            Cell cell2 = row.createCell(1);
            cell2.setCellValue(item.getvSpaModel());

            Cell cell3 = row.createCell(2);
            cell3.setCellValue(item.getvSelectBrand());

            Cell cell4 = row.createCell(3);
            cell4.setCellValue(item.getvSuggestModel());

            Cell cell5 = row.createCell(4);
            cell5.setCellValue(item.getvSuggestName());

            Cell cell6 = row.createCell(5);
            cell6.setCellValue(item.getvSuggestModel());

            Cell cell7 = row.createCell(6);
            cell7.setCellValue(item.getnSuggestPrice().toString());

            Cell cell8 = row.createCell(7);
            cell8.setCellValue("");
        }
        return sheetNew;
    }



}
